import datetime

import pymysql
# 获取连接对象conn，建立数据库的连接
conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='root3838', db='three')  # db:表示数据库名称
cursor = conn.cursor()


# 3、就诊卡管理信息系统
# （1）开卡功能。设置姓名、年龄、电话、性别等字段。（20分）
# （2）挂号功能。增加就诊科室和预约就诊时间两个信息。（10分）
# （3）就诊功能。增加医生建议信息。（20分）
# （4）查询功能。查询看病记录。（10分）
# （5）销卡功能。（10分）
# （6）退出系统。（10分）
# （7）将就诊信息写入csv表格或者数据库。（20分）

def print_menu():
    print('=' * 30)
    print('就诊卡管理信息系统')
    print('1.开卡')
    print('2.挂号')
    print('3.就诊')
    print('4.查询')
    print('5.销卡')
    print('0.退出系统')


def addCard():
    new_name = input('请输入你的姓名：')
    new_sex = input('请输入你的性别：')
    new_age = input('请输入你的年龄：')
    new_phone = input('请输入你的电话号码：')
    sql = 'insert into card(name,sex,age,phone) values(%s,%s,%s,%s)'
    cursor.execute(sql, (new_name, new_sex, new_age, new_phone))
    print("开卡成功！您的卡号为：" + str(conn.insert_id()))
    conn.commit()



def addRegistration():
    new_cardid = int(input('请输入你的卡号：'))
    cursor.execute('select * from card where cardid = %s;', new_cardid)
    if not cursor.fetchone():
        print("该卡不存在！")
        return
    new_department = input('请输入就诊部门名称：')
    new_clinictime = input('请输入就诊时间(例如：2021-03-08 23:59:59)：')
    new_clinictime = datetime.datetime.strptime(new_clinictime, "%Y-%m-%d %H:%M:%S")
    sql = 'insert into registration(cardid,department,clinictime) values(%s,%s,%s)'
    cursor.execute(sql, [new_cardid, new_department, new_clinictime])

    cursor.execute('select * from registration where id = %s;', conn.insert_id())
    print('您新增的看病记录如下：')
    print('=' * 30)
    print('%-5s%-5s%-14s%-17s%-20s' % ('序号', '卡号', '就诊科室', '就诊时间', '医生建议'))
    registrationList = cursor.fetchall()
    for registration in registrationList:
        print("%-6d%-6d%-15s%-15s %-20s" % registration)

    conn.commit()



def delCard():
    cardid = int(input('请输入你要销卡的卡号：'))
    sql = "delete from card where cardid = %s;"
    cursor.execute(sql, cardid)
    conn.commit()
    print("销卡成功！")


def modifyAdvice():
    registrationId = int(input('请输入要修改看病记录的序号：'))
    advice = input('请输入医生建议：')
    sql = 'update registration set advice = %s where id = %s'
    rows = cursor.execute(sql, (advice, registrationId))
    conn.commit()


def showRegistration():
    cardid = int(input('请输入你的卡号：'))
    rows = cursor.execute('select * from registration where cardid = %s;', cardid)
    print('您的看病记录信息如下：')
    print('=' * 30)
    print('%-5s%-5s%-14s%-17s%-20s' % ('序号', '卡号', '就诊科室', '就诊时间', '医生建议'))
    registrationList = cursor.fetchall()
    for registration in registrationList:
        print("%-6d%-6d%-15s%-15s %-20s" % registration)


if __name__ == '__main__':
    while True:
        print_menu()
        key = input("请输入功能对应的数字：")
        if key == '1':
            addCard()
        elif key == '2':
            addRegistration()
        elif key == '3':
            modifyAdvice()
        elif key == '4':
            showRegistration()
        elif key == '5':
            delCard()
        elif key == '0':
            quit_confirm = input('亲，请您确定，真的要退出吗？(Yes or No):')
            if quit_confirm == 'Yes':
                break
            else:
                print('输入有误，请重新输入')
